﻿//author: Sérgio Severino, nº 9733

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Text;
using System.IO;

using Devart.Data; 
//Devart.Data.dll
//from: http://www.devart.com/dotconnect/sqlite/

using Devart.Data.SQLite; 
//Devart.Data.SQLite.dll
//from: http://www.devart.com/dotconnect/sqlite/

using LumenWorks.Framework.IO.Csv; 
//LumenWorks.Framework.IO.dll
//from: http://www.codeproject.com/KB/database/CsvReader.aspx

namespace GUI_3._5
{
    /*
     * The class that manages all the sqlite and csv files work
     */
    public class Database
    {
        public static string DATABASE_NAME = "database.db";

        private string dbConnStr; //connection string
        private List<string> differentYears;
        private List<string> differentEstablishmentName;
        private List<string> differentDegree;
        private IEnumerable<List<string>> database;

        /*
         * A static method that converts a set of csv files
         * inside a folder to a sqlite database
         * receives a folder name
         * receives a sqlite filename
         */
        public static void createDatabase(string folder, string databaseName)
        {
            string appPath = Application.StartupPath + "/" + folder;
            string fileExtension = "*.csv";
            

            // create a new database connection
            SQLiteConnection sqlite_conn = new SQLiteConnection();
            sqlite_conn.ConnectionString = "Data Source=" + databaseName + ";FailIfMissing=false;";
            
            sqlite_conn.Open();
            
            // BEGIN DEFERRED for faster insert
            SQLiteCommand sqlite_cmd = new SQLiteCommand("BEGIN", sqlite_conn);
            sqlite_cmd.ExecuteNonQuery();
            
            sqlite_cmd = new SQLiteCommand("DROP TABLE IF EXISTS tabela", sqlite_conn);
            sqlite_cmd.ExecuteNonQuery();

            // Let the SQLiteCommand object know our SQL-Query:
            sqlite_cmd.CommandText = "CREATE TABLE tabela (" +
                                                "CodigodoDocente varchar, " +
                                                "Nome varchar, " +
                                                "UltimoGrau varchar, " +
                                                "Curso varchar, " +
                                                "Categoria varchar, " +
                                                "RegimeDeTempo varchar, " +
                                                "AnoLectivo varchar, " +
                                                "TipoDeEstabelecimento varchar, " +
                                                "NomeDoEstabelecimento varchar" +
                                                ");";

            // Now lets execute the SQL ;D
            sqlite_cmd.ExecuteNonQuery();

            DirectoryInfo dir = new DirectoryInfo(appPath);
            try
            {
                FileInfo[] files = dir.GetFiles(fileExtension);
                foreach (FileInfo file in files)
                {
                    CsvReader csv = new CsvReader(new StreamReader(file.FullName), true /*headers*/);
                    foreach (var line in csv)
                    {
                        sqlite_cmd.CommandText = "INSERT INTO tabela (CodigodoDocente, " +
                                                        "Nome, " +
                                                        "UltimoGrau, " +
                                                        "Curso, " +
                                                        "Categoria, " +
                                                        "RegimeDeTempo, " +
                                                        "AnoLectivo, " +
                                                        "TipoDeEstabelecimento, " +
                                                        "NomeDoEstabelecimento) " +
                                                 "VALUES (" +
                                                        "'" + line[0].Replace('\'', '☼') + "'," +
                                                        "'" + line[1].Replace('\'', '☼') + "'," +
                                                        "'" + line[2].Replace('\'', '☼') + "'," +
                                                        "'" + line[3].Replace('\'', '☼') + "'," +
                                                        "'" + line[4].Replace('\'', '☼') + "'," +
                                                        "'" + line[5].Replace('\'', '☼') + "'," +
                                                        "'" + line[6].Replace('\'', '☼') + "'," +
                                                        "'" + line[7].Replace('\'', '☼') + "'," +
                                                        "'" + line[8].Replace('\'', '☼') + "'" +
                                                        ");";

                        sqlite_cmd.ExecuteNonQuery();
                    }
                }
            }
            catch
            {}
            finally
            {
                sqlite_conn.Commit();
                sqlite_conn.Close();
            }
        }

        /*
         * Main constructor
         */
        public Database()
        {
            differentYears = new List<string>();
            differentEstablishmentName = new List<string>();
            differentDegree = new List<string>();

        }

        /* This is the method that
         * opens the sqlite database and copies it to a list
         */
        public void openDatabase(string databaseName)
        {
            dbConnStr = "Data Source=" + databaseName + ";FailIfMissing=false;";

            // create a new database connection
            SQLiteConnection sqlite_conn = new SQLiteConnection();
            sqlite_conn.ConnectionString = dbConnStr;
            sqlite_conn.Open();

            // BEGIN DEFERRED
            SQLiteCommand sqlite_cmd = new SQLiteCommand("BEGIN", sqlite_conn);
            sqlite_cmd.ExecuteNonQuery();

            sqlite_cmd.CommandText = "SELECT * FROM tabela";

            // DataReader
            SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();

            List<List<string>> tempList = new List<List<string>>();

            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
            {
                List<string> tempShortList = new List<string>();

                // Print out the content of the text field:
                for (int f = 0; f < sqlite_datareader.FieldCount; f++)
                    tempShortList.Add(((string)sqlite_datareader[f]).Replace('☼', '\''));
                tempList.Add(tempShortList);
            }

            database = from tempShortList in tempList
                       select tempShortList;

            populateDiffYears(sqlite_conn);
            populateDiffEstablishmentName(sqlite_conn);
            populateDiffDegree(sqlite_conn);

            sqlite_cmd.CommandText = "END";
            sqlite_cmd.ExecuteNonQuery();

            sqlite_conn.Close();
        }

        /*
         * This method populates a list with all 
         * the different years in the database
         */
        private void populateDiffYears(SQLiteConnection sqlite_conn)
        {
            SQLiteCommand sqlite_cmd = new SQLiteCommand("SELECT DISTINCT AnoLectivo FROM tabela", sqlite_conn);

            // DataReader
            SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();

            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
            {
                List<string> tempShortList = new List<string>();

                // Print out the content of the text field:
                for (int f = 0; f < sqlite_datareader.FieldCount; f++)
                    differentYears.Add(((string)sqlite_datareader[f]).Replace('☼', '\''));
            }
            differentYears.OrderBy(c => c.ToString());
        }

        /*
         * This method populates a list with all 
         * the different establishment names in the database
         */
        private void populateDiffEstablishmentName(SQLiteConnection sqlite_conn)
        {
            SQLiteCommand sqlite_cmd = 
                new SQLiteCommand("SELECT DISTINCT NomeDoEstabelecimento FROM tabela", sqlite_conn);

            // DataReader
            SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();

            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
            {
                List<string> tempShortList = new List<string>();

                // Print out the content of the text field:
                for (int f = 0; f < sqlite_datareader.FieldCount; f++)
                    differentEstablishmentName.Add(((string)sqlite_datareader[f]).Replace('☼', '\''));
            }
            differentEstablishmentName.OrderBy(c => c.ToString());
        }

        /*
         * This method populates a list with all 
         * the different degrees in the database
         */
        private void populateDiffDegree(SQLiteConnection sqlite_conn)
        {
            SQLiteCommand sqlite_cmd = 
                new SQLiteCommand("SELECT DISTINCT UltimoGrau FROM tabela", sqlite_conn);

            // DataReader
            SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();

            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
            {
                List<string> tempShortList = new List<string>();

                // Print out the content of the text field:
                for (int f = 0; f < sqlite_datareader.FieldCount; f++)
                    differentDegree.Add(((string)sqlite_datareader[f]).Replace('☼', '\''));
            }
            differentDegree.OrderBy(c => c.ToString());
        }

        /*
         * Return method for the list differentYears
         */
        public List<string> getDifYears()
        {
            return this.differentYears;
        }

        /*
         * Return method for the list differentEstablishmentName
         */
        public List<string> getDifEstablishmentName()
        {
            return this.differentEstablishmentName;
        }

        /*
         * Return method for the list differentDegree
         */
        public List<string> getDifDegree()
        {
            return this.differentDegree;
        }

        /*
         * This method produces a list with all teachers in
         * the given argument
         */
        public void createListCsvTpDpY(int selectedEstablishment, int selectedDegree)
        {
            string establishment = this.getDifEstablishmentName()[selectedEstablishment];
            string degree = this.getDifDegree()[selectedDegree];

            var list = from tmpList in this.database
                       where tmpList[8] == establishment && tmpList[2] == degree
                       orderby tmpList[2], tmpList[8], tmpList[6], tmpList[1] ascending
                       select new string[] { tmpList[2], tmpList[8], tmpList[6], tmpList[1] };

            string filename = "list of teachers, " + establishment + " per " + degree + ".csv";
            string header = "Último Grau," +
                            "Nome Do Estabelecimento," +
                            "Ano Lectivo," +
                            "Nome do Professor";

            this.writeCsv(list, filename, header);
        }

        /* 
         * This method produces the statistics about
         * the given argument
         */
        public void createCsvTpDpY(int selectedEstablishment, int selectedDegree)
        {
            string establishment = this.getDifEstablishmentName()[selectedEstablishment];
            string degree = this.getDifDegree()[selectedDegree];

            List<List<string>> counter = new List<List<string>>();
            foreach (var year in this.getDifYears())
            {
                int quantity = (from tmpList in this.database
                                where tmpList[8] == establishment && tmpList[6] == year && tmpList[2] == degree
                                select tmpList).Count();

                List<string> tmp = new List<string>();
                tmp.Add(year);
                tmp.Add(quantity.ToString());

                counter.Add(tmp);
            }

            var list = from quantity in counter
                       select new string[] { degree, establishment, quantity[0], quantity[1] };


            string filename = establishment + " per " + degree + ".csv";
            string header = "Último Grau," +
                            "Nome Do Estabelecimento," +
                            "Ano Lectivo," +
                            "Quantidade de Professores";

            this.writeCsv(list, filename, header);
        }

        /*
         * This method produces a list with all teachers in
         * the given argument
         */
        public void createListCsvTpY(int selectedEstablishment)
        {
            string establishment = this.getDifEstablishmentName()[selectedEstablishment];

            var list = from tmpList in this.database
                       where tmpList[8] == establishment
                       orderby tmpList[8], tmpList[6], tmpList[1] ascending
                       select new string[] { tmpList[8], tmpList[6], tmpList[1] };

            string filename = "list of teachers, " + establishment + " per Year.csv";
            string header = "Nome Do Estabelecimento," +
                            "Ano Lectivo," +
                            "Nome do Professor";

            this.writeCsv(list, filename, header);
        }

        /* 
         * This method produces the statistics about
         * the given argument
         */
        public void createCsvTpY(int selectedEstablishment)
        {
            string establishment = this.getDifEstablishmentName()[selectedEstablishment];

            List<List<string>> counter = new List<List<string>>();
            foreach (var year in this.getDifYears())
            {
                int quantity = (from tmpList in this.database
                                where tmpList[8] == establishment && tmpList[6] == year
                                select tmpList).Count();

                List<string> tmp = new List<string>();
                tmp.Add(year);
                tmp.Add(quantity.ToString());

                counter.Add(tmp);
            }

            var list = from quantity in counter
                       select new string[] { establishment, quantity[0], quantity[1] };


            string filename = establishment + " per Year.csv";
            string header = "Nome Do Estabelecimento," +
                            "Ano Lectivo," +
                            "Quantidade de Professores";

            this.writeCsv(list, filename, header);
        }

        /*
         * This method creates a csv file 
         */
        private void writeCsv(IEnumerable<string[]> list, string filename, string header)
        {
            File.Delete(filename);
            using (StreamWriter sw = new StreamWriter(filename, true))
            {
                sw.WriteLine(header);
                foreach (var tempList in list)
                {
                    StringBuilder str = new StringBuilder();
                    foreach (var temp in tempList)
                        str.Append(temp + ",");
                    str.Remove(str.ToString().Length - 1, 1);
                    sw.WriteLine(str.ToString());
                }
            }
        }
    }


    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1(new Database(), Database.DATABASE_NAME));
        }
    }
}
